package com.gmail.blackdog1987.onedollar.dao;

import com.gmail.blackdog1987.onedollar.dao.hibernate.EntityHibernateDao;
import com.gmail.blackdog1987.onedollar.dao.hibernate.jdbc.BaseJdbcDao;
import com.gmail.blackdog1987.onedollar.dao.hibernate.jdbc.JdbcUtil;
import com.gmail.blackdog1987.onedollar.entity.TLuckybagConsumeEntity;
import com.gmail.blackdog1987.onedollar.entity.TLuckybagEntity;
import com.gmail.blackdog1987.onedollar.entity.base.Page;
import com.gmail.blackdog1987.onedollar.vo.GoodsOneDollarVO;
import com.gmail.blackdog1987.onedollar.vo.LuckyBagVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Repository("luckyBagConsumeDao")
public class LuckyBagConsumeDao extends EntityHibernateDao<TLuckybagConsumeEntity>{
    @Autowired
    private BaseJdbcDao baseJdbcDao;
    public TLuckybagConsumeEntity consume(String userid, String luckybagid){
        String sql = "SELECT t.*\n" +
                "FROM t_luckybag_consume t\n" +
                "WHERE t.luckybag_id = ?\n" +
                "  AND t.status = 0\n" +
                "  AND t.luckybag_id NOT IN\n" +
                "    (SELECT h.luckybag_id\n" +
                "     FROM t_luckybag_consume_history h\n" +
                "     WHERE h.user_id = ?)\n" +
                "ORDER BY t.orderby";

        List<TLuckybagConsumeEntity> list = JdbcUtil.queryForList(baseJdbcDao, TLuckybagConsumeEntity.class, sql, new String[]{luckybagid, userid});

        return list==null||list.size()==0?null:list.get(0);
    }

    public Page<LuckyBagVO> queryConsumeForPage(String userid, int pageindex, int pagesize, int year){
        String whereSql;
        ArrayList<Object> whereArgs = new ArrayList<Object>();
        whereSql = " FROM t_luckybag_consume\n" +
                "LEFT JOIN t_luckybag ON t_luckybag_consume.luckybag_id = t_luckybag.id\n" +
                "LEFT JOIN t_user on t_luckybag.sender = t_user.user_id\n" +
                "WHERE consumer = ? AND consumetime >= ? AND consumetime <= ?\n" +
                "ORDER BY consumetime DESC ";

        String selectionSql = " SELECT t_luckybag_consume.*,\n" +
                "       t_luckybag.content,\n" +
                "       t_luckybag.sendtime,\n" +
                "       t_user.nick_name,\n" +
                "       t_user.head ";

        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) " + whereSql , new String[]{userid, year + "-01-01 00:00:00", year+"-12-31 11:59:59"});

        List<LuckyBagVO> list = JdbcUtil.queryForList(baseJdbcDao, LuckyBagVO.class, selectionSql + whereSql + limit, new String[]{userid, year + "-01-01 00:00:00", year+"-12-31 11:59:59"});

        Page page = new Page(list, pageindex, totalCount, pagesize);

        return page;
    }

//    public Page<LuckyBagVO> querySendForPage(String userid, int pageindex, int pagesize, int year){
//        String whereSql;
//        ArrayList<Object> whereArgs = new ArrayList<Object>();
//        whereSql = "FROM t_luckybag_consume\n" +
//                "LEFT JOIN t_luckybag ON t_luckybag_consume.luckybag_id = t_luckybag.id\n" +
//                "LEFT JOIN t_user u on t_luckybag.sender = u.user_id\n" +
//                "LEFT JOIN t_user us on t_luckybag_consume.consumer = us.user_id\n" +
//                "WHERE t_luckybag.sender = ? AND t_luckybag_consume.status = 1 AND consumetime >= ? AND consumetime <= ?\n" +
//                "ORDER BY consumetime DESC ";
//
//        String selectionSql = "SELECT t_luckybag_consume.*,\n" +
//                "       t_luckybag.content,\n" +
//                "       t_luckybag.sendtime,\n" +
//                "       us.nick_name sender_name,\n"+
//                "       us.head ";
//
//        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
//        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) " + whereSql , new String[]{userid, year + "-01-01 00:00:00", year+"-12-31 11:59:59"});
//
//        List<LuckyBagVO> list = JdbcUtil.queryForList(baseJdbcDao, LuckyBagVO.class, selectionSql + whereSql + limit, new String[]{userid, year + "-01-01 00:00:00", year+"-12-31 11:59:59"});
//
//        Page page = new Page(list, pageindex, totalCount, pagesize);
//
//        return page;
//    }

    public Page<LuckyBagVO> querySendForPage(String userid, int pageindex, int pagesize, int year){
        String whereSql;
        ArrayList<Object> whereArgs = new ArrayList<Object>();
        whereSql = "FROM t_luckybag\n" +
                "LEFT JOIN t_user ON t_luckybag.sender = t_user.user_id\n" +
                "WHERE t_luckybag.sender = ? AND sendtime >= ? AND sendtime <= ?\n" +
                "ORDER BY sendtime DESC ";

        String selectionSql = "SELECT id luckybag_id,\n" +
                "       total money,\n" +
                "       content,\n" +
                "       nick_name sender_name,\n" +
                "       nick_name,\n" +
                "       head,\n" +
                "       sendtime ";

        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) " + whereSql , new String[]{userid, year + "-01-01 00:00:00", year+"-12-31 11:59:59"});

        List<LuckyBagVO> list = JdbcUtil.queryForList(baseJdbcDao, LuckyBagVO.class, selectionSql + whereSql + limit, new String[]{userid, year + "-01-01 00:00:00", year+"-12-31 11:59:59"});

        Page page = new Page(list, pageindex, totalCount, pagesize);

        return page;
    }

    public int queryNewCount(String userid){

        String sql = "SELECT count(*) from\n" +
                "  (SELECT t.id\n" +
                "   FROM t_luckybag t, t_luckybag_consume c\n" +
                "   WHERE t.id = c.luckybag_id\n" +
                "     AND t.`type` = 2\n" +
                "     AND t.availablecount >= 1\n" +
                "     AND c.consumer = ?\n" +
                "   UNION ALL SELECT t2.id\n" +
                "   FROM t_luckybag t2\n" +
                "   WHERE t2.type <2\n" +
                "     AND t2.availablecount >= 1\n" +
                "     AND t2.id NOT IN\n" +
                "       (SELECT luckybag_id\n" +
                "        FROM t_luckybag_consume_history\n" +
                "        WHERE user_id=?)) r";

        return baseJdbcDao.getJdbcTemplate().queryForInt(sql, new String[]{userid, userid});
    }

    public LuckyBagVO queryNewest(String userid){

        String whereSql =
                "FROM t_luckybag_consume\n" +
                "LEFT JOIN t_luckybag ON t_luckybag_consume.luckybag_id = t_luckybag.id\n" +
                "LEFT JOIN t_user ON t_luckybag.sender = t_user.user_id\n" +
                "WHERE t_luckybag_consume.status = 0\n" +
                "  AND t_luckybag_consume.luckybag_id NOT IN\n" +
                "    (SELECT luckybag_id\n" +
                "     FROM t_luckybag_consume_history\n" +
                "     WHERE user_id = ?)\n" +
                "  AND (t_luckybag_consume.consumer = ?\n" +
                "  OR t_luckybag_consume.consumer IS NULL) LIMIT 1";

        String selectionSql =
                "SELECT t_luckybag_consume.*,\n" +
                "       t_luckybag.content,\n" +
                "       t_luckybag.sendtime,\n" +
                "       t_user.nick_name,\n"+
                "       t_user.head\n";
        List<LuckyBagVO> list = JdbcUtil.queryForList(baseJdbcDao, LuckyBagVO.class, selectionSql + whereSql, new String[]{userid,userid});
        return list==null||list.size()==0?null:list.get(0);
    }

    public List<LuckyBagVO> queryNew(String userid){
        String selectionSql =
                "SELECT r.*,\n" +
                        "       t_user.nick_name,\n" +
                        "       t_user.head\n" +
                        "FROM\n" +
                        "  (SELECT t.*\n" +
                        "   FROM t_luckybag t,\n" +
                        "        t_luckybag_consume c\n" +
                        "   WHERE t.id = c.luckybag_id\n" +
                        "     AND t.`type` = 2\n" +
                        "     AND t.availablecount >= 1\n" +
                        "     AND c.consumer = ?\n" +
                        "   UNION ALL SELECT t2.*\n" +
                        "   FROM t_luckybag t2\n" +
                        "   WHERE t2.type <2\n" +
                        "     AND t2.availablecount >= 1\n" +
                        "     AND t2.id NOT IN\n" +
                        "       (SELECT luckybag_id\n" +
                        "        FROM t_luckybag_consume_history\n" +
                        "        WHERE user_id=?)) r\n" +
                        "LEFT JOIN t_user ON r.sender = t_user.user_id";
        List<LuckyBagVO> list = JdbcUtil.queryForList(baseJdbcDao, LuckyBagVO.class, selectionSql, new String[]{userid,userid});
        return list;
    }

    public void disableNotReceiveBeforeTime(Date time){
        String selectionSql = "update t_luckybag_consume set `status` = 2 where `status`=0 and luckybag_id in (select id from t_luckybag where sendtime <= ?)";
        baseJdbcDao.getJdbcTemplate().update(selectionSql, new Object[]{time});
    }
}
